package com.hnkywl.sts.dao.report;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by ccr on 2014/5/19.
 */
@Repository
public class BzjlxfpDao extends SimpleHibernateTemplate{
    public Map<String,Object> bzjlxfpList(Long gsId,Long khId,String htbm,int pageNo,int pageSize){
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String columnSelect = "select gsid \"gsid\",gs \"gs\",khid \"khid\",kh \"kh\",htbm \"htbm\", bzj \"bzj\"," +
                "zlx \"zlx\",ysy \"ysy\" ,sy \"sy\"";
        String groupBy = " group by gs.id,kh.id,gs.jc,kh.jc,ht.htbm";
        String from = "select gs.id gsid,gs.jc gs,kh.id khid,kh.jc kh,ht.htbm htbm,sum(bzj.je) bzj,sum(nvl(lxfp.lx, 0)) zlx,round(nvl(sum(xsmx.sylx), 0), 4) ysy," +
                " sum(nvl(lxfp.lx, 0)) - round(nvl(sum(xsmx.sylx), 0), 4) sy " +
                " from t_biz_bzj bzj " +
                "       left join (select bzj_id,sum(lx) lx from (select distinct lx,jsrq,bzj_id from t_biz_lxfp) group by bzj_id) lxfp on bzj.id = lxfp.bzj_id " +
                "       left join t_sys_gsjg gs on bzj.nbgs_id = gs.id " +
                "       left join t_sys_gsjg kh on bzj.kh_id = kh.id " +
                "       left join t_biz_ht ht on ht.id = bzj.ht_id " +
                "       left join (select b.yck,e.cght_id, b.sbgs_id, b.kh_id, a.ck_sbsl * nvl(a.yhjg, 0) sylx " +
                "                      from t_biz_xsmx a, t_biz_xsd b, t_sys_gsjg c,t_biz_kh d,t_biz_cgd e " +
                "                     where a.xsd_id = b.id " +
                "                       and b.sbgs_id = c.id and a.kh_id = d.id and e.id = d.cgd_id and b.yck = 1" +
                "                       and c.type = 'nbgs') xsmx on gs.id = xsmx.sbgs_id and xsmx.kh_id = kh.id and xsmx.cght_id = ht.id";
        String where = "";
        condition.add(" bzj.deleted = 0 ");
        //condition.add(" bzj.status = 1 ");
        if (gsId != null) {
            condition.add(" gs.id = :wly ");
            param.put("wly", gsId);
        }
        if (khId != null) {
            condition.add(" kh.id = :kh ");
            param.put("kh", khId);
        }
        if (StringUtils.isNotEmpty(htbm)) {
            condition.add(" ht.htbm like :htbm ");
            param.put("htbm", htbm+"%");
        }
        if (!condition.isEmpty())
            where = " where " + StringUtils.join(condition, " and ");
        SQLQuery cntQuery = getSession().createSQLQuery("select count(1) from (" + from + where + groupBy + ")");
        SQLQuery rowQuery = getSession().createSQLQuery(columnSelect + " from (" + from + where + groupBy + ")");
        SQLQuery sumQuery = getSession().createSQLQuery("select '合计' \"gs\", sum(bzj) \"bzj\",sum(zlx) \"zlx\",sum(ysy) \"ysy\",sum(sy) \"sy\" from (" + from + where + groupBy + ")");
        int c = ((BigDecimal)cntQuery.setProperties(param).uniqueResult()).intValue();
        List<Map<String,Object>> r = rowQuery.setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .setFirstResult((pageNo - 1) * pageSize)
                .setMaxResults(pageSize)
                .list();
        List<Map<String,Object>> s = sumQuery.setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        Map<String,Object> result = new HashMap<String, Object>();
        result.put("count",c);
        result.put("rows",r);
        result.put("footer",s);
        return result;
    }
}
